import requests
from flask import Flask, request, jsonify,send_file, make_response
import mysql.connector
from mysql.connector import Error
from datetime import datetime
import pandas as pd
import openpyxl
import io
app = Flask(__name__)



# 数据库配置信息
DB_CONFIG = {
    'host': '124.223.105.49',
    'user': 'root',
    'password': 'rootSql',
    'database': 'mydatabase',
    'charset':'utf8mb4'
}

# 连接数据库
def get_db_connection():
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        return conn
    except Error as e:
        return str(e)

# 创建表
# def create_table():
#     create_table_sql = """
#     CREATE TABLE IF NOT EXISTS danmu_table (
#         id INT AUTO_INCREMENT PRIMARY KEY,
#         sender VARCHAR(255),
#         content TEXT,
#         status VARCHAR(255),
#         style_type VARCHAR(255),
#         send_time DATETIME,
#         update_time TIMESTAMP
#     )
#     CREATE TABLE IF NOT EXISTS user_table (
#         id INT AUTO_INCREMENT PRIMARY KEY,
#         openid TEXT,
#         avatarUrl TEXT,
#         nickName TEXT,
#         sendCount VARCHAR(255),
#         update_time TIMESTAMP
#     )
#     """
#     conn = get_db_connection()
#     cursor = conn.cursor()
#     cursor.execute(create_table_sql)
#     conn.commit()
#     cursor.close()
#     conn.close()


# 微信公众号的AppID和AppSecret
APPID = 'wx6dfe905ccb6e4f8f'
APPSECRET = '6164cc1cc4adbc570af49c634a9adf12'


def get_access_token(code):
    url = f"https://api.weixin.qq.com/sns/oauth2/access_token?appid={APPID}&secret={APPSECRET}&code={code}&grant_type=authorization_code"
    response = requests.get(url)
    if response.status_code == 200:
        result = response.json()
        if 'access_token' in result:
            access_token = result['access_token']
            return access_token
        else:
            return jsonify({'error': f"获取access_token失败{result['errmsg']}"}), 500


def get_user_info(code):
    access_token = get_access_token(code)
    if isinstance(access_token, str):
        # url = f"https://api.weixin.qq.com/wxa/business/getuserphonenumber?access_token={access_token}"
        url = f"https://api.weixin.qq.com/sns/userinfo?access_token={access_token}&openid=OPENID&lang=zh_CN"
        headers = {
            'Content-Type': 'application/json',
        }
        response = requests.post(url)
        if response.status_code == 200:
            result = response.json()
            return jsonify(result), 200
        else:
            return jsonify({'error': '请求失败'}), response.status_code
    else:
        return access_token

@app.route('/api/getUserinfo', methods=['GET'])
def api_get_userinfo():
    code = request.args.get('code')
    if not code:
        return jsonify({'error': '缺少code参数'}), 400
    return get_user_info(code)


# 弹幕部分
# 插入数据
@app.route('/api/danmu/add', methods=['POST'])
def add_danmu():
    data = request.json
    conn = get_db_connection()
    cursor = conn.cursor()
    # 查询user_table表的SQL语句，检查sendCount是否已经达到1000
    check_sql = """
        SELECT sendCount FROM user_table WHERE phone = %s
        """
    cursor.execute(check_sql, (data['sender'],))
    result = cursor.fetchone()  # 获取查询结果
    sendcount = result[0] if result else 0  # 如果查询结果不为空，则赋值给sendCount，否则默认为0
    # 尝试将sendCount_str转换为整数
    try:
        sendcount = int(sendcount)
    except ValueError:
        # 如果转换失败，将sendCount设置为0
        sendcount = 0

    # 如果sendCount已经达到99999，返回错误消息
    if sendcount >= 99999:
        return jsonify({"message": "不能继续发送，已达上限"}), 400

    insert_sql = """
    INSERT INTO danmu_table (sender, content, status, style_type, send_time)
    VALUES (%s, %s, %s, %s, %s)
    """
    print(data['sender'])
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    cursor.execute(insert_sql, (data['sender'], data['content'], -1 , data['style_type'], timestamp))

    # 更新user_table表的SQL语句
    update_sql = """
       UPDATE user_table
       SET sendCount = sendCount + 1
       WHERE phone = %s
       """
    cursor.execute(update_sql, (data['sender'],))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({"message": "新增成功"}), 200

# 查询数据
@app.route('/api/danmu', methods=['POST'])
def get_danmu():
    data = request.json
    page_size = data['page_size']
    page = data['page']
    status = data['status']
    conn = get_db_connection()
    cursor = conn.cursor()

    offset = (page - 1) * page_size
    cursor.execute("""
    SELECT * FROM danmu_table where status = %s order by id desc LIMIT %s OFFSET %s 
    """, (status,page_size, offset))
    rows = cursor.fetchall()

    # 获取总记录数
    cursor.execute(f"SELECT COUNT(*) FROM danmu_table where status = {status}")
    total_rows = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    data = [{"id": row[0], "sender": row[1], "content": row[2], "status": row[3], "send_time": row[5],
             "update_time": row[6] } for row in rows]

    # 封装分页结果
    pagination_data = {
        "total": total_rows,
        "total_pages": (total_rows + page_size - 1) // page_size,  # 计算总页数
        "page": page,
        "page_size": page_size,
        "data": data
    }

    return jsonify(pagination_data)

# 查询数据
@app.route('/api/danmuactive', methods=['GET'])
def get_danmu_active():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
       SELECT * FROM danmu_table WHERE update_time >= NOW() - INTERVAL 30 second and status = 1
    """)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    data = [{"id": row[0], "sender": row[1], "content": row[2], "status": row[3], "style_type":row[4],"send_time": row[5], "update_time": row[6]} for row in rows]
    return jsonify(data)

# 查询可用100s外数据10条
@app.route('/api/danmuactiveold', methods=['GET'])
def get_danmu_activeold():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
       SELECT * FROM danmu_table WHERE update_time < NOW() - INTERVAL 30 second and status = 1
                ORDER BY RAND()  LIMIT 10
    """)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    data = [{"id": row[0], "sender": row[1], "content": row[2], "status": row[3], "style_type":row[4],"send_time": row[5], "update_time": row[6]} for row in rows]
    return jsonify(data)


# 更新数据
@app.route('/api/danmu/update/<int:id>', methods=['PUT'])
def update_danmu(id):
    data = request.json
    conn = get_db_connection()
    cursor = conn.cursor()
    update_sql = """
    UPDATE danmu_table SET
        status = %s
    WHERE id = %s
    """
    cursor.execute(update_sql, (data['status'],id))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({"message": "更新成功"})



# 用户部分
# 查询用户
@app.route('/api/user/query', methods=['GET'])
def get_user_query():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
      select  ut.avatarUrl,ut.nickName,ut.phone from danmu_table dt left join  user_table ut on dt.sender = ut.phone  ORDER BY RAND() limit 1000
    """)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    data = [{"avatarUrl": row[0], "nickName": row[1], "phone": row[2]} for row
            in rows]
    return jsonify(data)
# 新增用户
@app.route('/api/user/add', methods=['POST'])
def add_user():
    data = request.json
    conn = get_db_connection()
    cursor = conn.cursor()
    insert_sql = """
    INSERT INTO user_table (openid, avatarUrl, nickName,sendCount)
    VALUES (%s, %s, %s, 0)
    """
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    cursor.execute(insert_sql, (data['openid'], data['avatarUrl'],  data['nickName']))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({"message": "新增成功"}),200

# 获取用户信息
@app.route('/api/user/getInfo', methods=['POST'])
def info_user():
    data = request.json
    conn = get_db_connection()
    cursor = conn.cursor()
    select_sql = f"select id,openid,avatarUrl,nickName,sendCount,update_time,phone from user_table where openid = '{data['openid']}'"
    print(select_sql)
    cursor.execute(select_sql)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    data = [
        {"id": row[0], "openid": row[1], "avatarUrl": row[2], "nickName": row[3], "sendCount": row[4], "update_time": row[5],"phone":row[6]
         } for row in rows]
    return jsonify(data)

# 更新用户手机号
@app.route('/api/user/update/<int:id>', methods=['PUT'])
def update_user(id):
    data = request.json
    conn = get_db_connection()
    cursor = conn.cursor()
    update_sql = """
   UPDATE user_table SET
        phone = %s
    WHERE id = %s
    """
    cursor.execute(update_sql, (data['phone'],id))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({"message": "更新成功"})



# 奖品部分
@app.route('/api/price/cj', methods=['POST'])
def pricecj():
    data = request.json
    conn = get_db_connection()
    cursor = conn.cursor()
    select_sql = f"""
        SELECT count(*) 
        FROM (
            SELECT *
            FROM danmu_table dt
            WHERE NOT EXISTS (
                SELECT 1
                FROM price_table pt
                WHERE pt.sender = dt.sender
            )
            
            GROUP BY dt.sender
            
            ORDER BY RAND()
        ) AS dt
    """
    cursor.execute(select_sql)
    rows = cursor.fetchall()
    if rows[0][0]<data['count']:
        return jsonify({'error': '失败'}), 400
    else:
        insert_sql = f"""
           INSERT INTO price_table (price_type, danmuid, sender, update_time)
            SELECT {data['price_type']}, dt.id, dt.sender, NOW()
            FROM (
                SELECT *
                FROM danmu_table dt
                WHERE NOT EXISTS (
                    SELECT 1
                    FROM price_table pt
                    WHERE pt.sender = dt.sender
                )
                
                GROUP BY dt.sender
                
                ORDER BY RAND()
                LIMIT {data['count']}
            ) AS dt
           """
        print(insert_sql);
        timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute(insert_sql)
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "新增成功"}), 200


@app.route('/api/price/cjCan', methods=['POST'])
def pricecjcan():
    data = request.json
    conn = get_db_connection()
    cursor = conn.cursor()
    select_sql = f"""
        SELECT count(*) 
        FROM (
            SELECT *
            FROM danmu_table dt
            WHERE NOT EXISTS (
                SELECT 1
                FROM price_table pt
                WHERE pt.sender = dt.sender
            )

            GROUP BY dt.sender

            ORDER BY RAND()
        ) AS dt
    """
    cursor.execute(select_sql)
    rows = cursor.fetchall()
    if rows[0][0] < data['count']:
        cursor.close()
        conn.close()
        return jsonify({'error': '抽奖人数大于未中奖总人数'}), 400
    else:
        cursor.close()
        conn.close()
        return jsonify({"can": True}), 200


@app.route('/api/price/query', methods=['GET'])
def get_price_query():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
       SELECT pt.price_type,usert.nickName,usert.avatarUrl,usert.phone ,danmu.content 
        FROM price_table pt
        
        LEFT JOIN user_table usert ON usert.phone = pt.sender
        LEFT JOIN danmu_table danmu ON pt.danmuid = danmu.id
        WHERE pt.update_time = (
            SELECT MAX(update_time)
            FROM price_table
        )
    """)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    data = [{"price_type": row[0], "nickName": row[1], "avatarUrl": row[2], "phone": row[3], "content":row[4]} for row in rows]
    return jsonify(data)


@app.route('/api/price/export', methods=['GET'])
def export_to_excel():
    # 将DataFrame转换为Excel文件
    conn = get_db_connection()
    cursor = conn.cursor()
    price_type_map = {
        0:'特等奖',
        1: '一等奖',
        2: '二等奖',
        3: '三等奖',
        4: '四等奖',
        5: '五等奖'
    }
    sql_query = f"""
        SELECT 
        CASE 
        WHEN pt.price_type = 0 THEN '特等奖'
        WHEN pt.price_type = 1 THEN '一等奖'
        WHEN pt.price_type = 2 THEN '二等奖'
        WHEN pt.price_type = 3 THEN '三等奖'
        WHEN pt.price_type = 4 THEN '四等奖'
        WHEN pt.price_type = 5 THEN '五等奖'
        ELSE '其他奖项'
    END AS prize_name,
        
        usert.nickName,usert.phone,pt.update_time time
            FROM price_table pt
            
            LEFT JOIN user_table usert ON usert.phone = pt.sender
            LEFT JOIN danmu_table danmu ON pt.danmuid = danmu.id
            
    """
    # 执行SQL查询
    cursor.execute(sql_query)

    # 获取查询结果
    records = cursor.fetchall()

    # 将查询结果转换为DataFrame
    df = pd.DataFrame(records, columns=['奖品', '用户','手机号','中奖时间'])  # 设置列名为“奖品”和“用户”

    # 将DataFrame转换为Excel文件
    output = io.BytesIO()
    df.to_excel(output, index=False)
    output.seek(0)

    # 创建一个响应对象，并将文件流设置为响应体
    response = make_response(send_file(output, as_attachment=True, download_name='中奖信息.xlsx',
                                       mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'))

    return response


@app.route('/api/price/list', methods=['POST'])
def get_price_list():
    data = request.json
    page_size = data['page_size']
    page = data['page']
    conn = get_db_connection()
    cursor = conn.cursor()

    offset = (page - 1) * page_size
    cursor.execute("""
   SELECT  CASE 
        WHEN pt.price_type = 0 THEN '特等奖'
        WHEN pt.price_type = 1 THEN '一等奖'
        WHEN pt.price_type = 2 THEN '二等奖'
        WHEN pt.price_type = 3 THEN '三等奖'
        WHEN pt.price_type = 4 THEN '四等奖'
        WHEN pt.price_type = 5 THEN '五等奖'
        ELSE '其他奖项'
    END AS prize_name,usert.nickName,usert.avatarUrl,usert.phone,pt.update_time 
FROM price_table pt
LEFT JOIN user_table usert ON usert.phone = pt.sender order by pt.update_time desc  LIMIT %s OFFSET %s 
    """, (page_size, offset))
    rows = cursor.fetchall()

    # 获取总记录数
    cursor.execute(f"SELECT COUNT(*) FROM price_table")
    total_rows = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    data = [{"prize_name": row[0], "nickName": row[1], "avatarUrl": row[2], "phone": row[3], "update_time": row[4]} for row in rows]

    # 封装分页结果
    pagination_data = {
        "total": total_rows,
        "total_pages": (total_rows + page_size - 1) // page_size,  # 计算总页数
        "page": page,
        "page_size": page_size,
        "data": data
    }

    return jsonify(pagination_data)

if __name__ == '__main__':
    # 创建表
    # create_table()
    # 运行Flask应用
    app.run(host='0.0.0.0',port=8000)